• Q and A
• Q and A - II
• Q and A - III
• MySQL: Table design and queries
• MySQL - Command - ii

Data Base

Q and A - II

1. What is the difference between a clustered and a non-clustered index?
   • Clustered Index: Rearranges the physical order of the table rows to match the index order. A table can have only one clustered index.
   • Non-Clustered Index: Stores index separately from the data. The table data remains unordered, but index entries contain pointers to the physical data location.

   Example:

   • CREATE CLUSTERED INDEX idx_emp_id ON employees(emp_id);
   • CREATE NONCLUSTERED INDEX idx_emp_name ON employees(name);
2. Explain the concept of normalization and its advantages.

   Normalization is the process of organizing data to reduce redundancy and improve data integrity. Advantages:

   • Minimizes duplication
   • Ensures consistency
   • Simplifies maintenance

   Example

   • 1NF: Eliminate repeating groups
   • 2NF: Remove partial dependencies
   • 3NF: Remove transitive dependencies

3. What is a transaction? What are ACID properties?

   A transaction is a logical unit of work in a database that must be either fully completed or fully rolled back. ACID:

   • Atomicity: All or none of the operations execute
   • Consistency: Database remains in a valid state
   • Isolation: Concurrent transactions do not affect each other
   • Durability: Changes persist even after system failure

4. What is a deadlock and how can it be prevented?

   A deadlock occurs when two or more transactions wait for each other indefinitely to release locks. Prevention methods:

   • Acquire locks in a consistent order
   • Implement deadlock detection and rollback one transaction
   • Use timeouts

5. Explain the difference between OLTP and OLAP systems.

   Feature	OLTP	OLAP
   Purpose	Day-to-day operations	Analytical processing
   Data	Current, detailed	Historical, summarized
   Queries	Simple, short	Complex, aggregate
   Example	Banking, e-commerce	Data warehouse, BI tools

6. What is a view? Can a view be updated?

   A view is a virtual table based on a SQL query. Updatable View: Possible if:

   • It references only one base table
   • Doesn't use aggregation or GROUP BY
   • Doesnt include DISTINCT, UNION, etc.
   Example:
   • CREATE VIEW v_emp AS SELECT emp_id, name FROM employees WHERE status='Active';

7. What are triggers? Give an example.

   A trigger is a stored procedure that automatically executes in response to certain events (INSERT, UPDATE, DELETE). Example:

   CREATE TRIGGER trg_audit

   AFTER DELETE ON employees

   FOR EACH ROW

   INSERT INTO audit_log(emp_id, deleted_at) VALUES(OLD.emp_id, NOW());

8. What are stored procedures and their advantages?

   A stored procedure is a precompiled SQL block stored in the database. Advantages:

   • Improves performance
   • Reduces network traffic
   • Enhances security and reusability

9. What is query optimization in DBMS?

   It's the process by which the database engine chooses the most efficient way to execute a query using execution plans, indexes, and statistics. Techniques:

   • Use appropriate indexes
   • Avoid SELECT *
   • Use JOINs efficiently

10. What is normalization vs denormalization?
    • Normalization: Splitting data to remove redundancy.
    • Denormalization: Combining tables for faster query performance at the cost of redundancy.
    Used in:
    • Normalization: OLTP
    • Denormalization: OLAP
11. Explain different types of database keys.
    • Primary Key: Uniquely identifies each record
    • Foreign Key: References another table's primary key
    • Candidate Key: Possible choices for primary key
    • Composite Key: Combination of columns forming a unique key
    • Alternate Key: Candidate key not selected as primary